package com.harmontronics.hanma.paas.paas.domain.releasesetup.service;


import cn.hutool.core.collection.CollUtil;
import com.harmontronics.hanma.paas.paas.domain.releasesetup.entity.script.*;
import com.harmontronics.hanma.paas.paas.domain.releasesetup.service.helper.Helper;
import com.harmontronics.hanma.paas.paas.domain.releasesetup.service.helper.HelperManager;
import org.apache.commons.codec.binary.Hex;

import java.io.File;
import java.io.FileWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import java.util.stream.Collectors;


/**
 * @author lflores
 */
public class ExportRunner {
    private final TableDefinition[] TABLES;
    private final ConnectionDefinitionBean SRC;
    private final ConnectionDefinitionBean DST;
    private final DBConnection CON_SRC;
//	private final DatabaseMetaData DB_META;

    private final Helper SRC_TR;
    private final Helper DEST_TR;
    private final boolean ignoreEmpty;
    private final boolean includeData = true;
    //支持flyway的所有表
    private final List<String> TABLE_DEFINITION_FLYWAY = CollUtil.list(false, "act_evt_log", "act_ge_bytearray", "act_ge_property", "act_hi_actinst", "act_hi_attachment"
            , "act_hi_comment", "act_hi_detail", "act_hi_entitylink", "act_hi_identitylink", "act_hi_procinst", "act_hi_taskinst",
            "act_hi_tsk_log", "act_hi_varinst", "act_id_bytearray", "act_id_group", "act_id_info", "act_id_membership",
            "act_id_priv", "act_id_priv_mapping", "act_id_property", "act_id_token", "act_id_user", "act_procdef_info", "act_re_deployment", "act_re_model",
            "act_re_procdef", "act_ru_actinst", "act_ru_deadletter_job", "act_ru_entitylink", "act_ru_event_subscr", "act_ru_execution", "act_ru_external_job", "act_ru_history_job", "act_ru_identitylink",
            "act_ru_job", "act_ru_suspended_job", "act_ru_task", "act_ru_timer_job", "act_ru_variable", "flw_channel_definition", "flw_ev_databasechangelog", "flw_ev_databasechangeloglock", "flw_event_definition", "flw_event_deployment",
            "flw_event_resource", "flw_ru_batch", "flw_ru_batch_part", "hm_cms_user", "hm_datasource", "hm_datasource_env", "hm_folder", "hm_http_request_definition",
            "hm_http_service", "hm_menu", "hm_model_publish", "hm_proc_inst_read", "hm_process", "hm_published_entity_model", "hm_script", "hm_ureport_file", "hm_user_report", "http_service_setting", "hm_entity_model",
            "hm_model", "hm_action", "hm_data_type", "hm_org", "hm_page", "hm_perm", "hm_perm_action", "hm_role", "hm_role_menu", "hm_role_perm", "hm_role_perm_action", "hm_user", "hm_user_group",
            "hm_user_group_data", "hm_user_group_manage", "hm_user_group_member", "hm_user_group_org", "hm_user_group_role", "hm_dictionary",
            "hm_user_org", "hm_user_org_serial", "qrtz_blob_triggers", "qrtz_calendars", "qrtz_cron_triggers", "qrtz_fired_triggers", "qrtz_scheduler_state", "qrtz_job_details", "qrtz_locks", "qrtz_paused_trigger_grps", "qrtz_scheduler_state",
            "qrtz_simple_triggers", "qrtz_simprop_triggers", "qrtz_triggers");
    //配置相关的表  需要全量同步数据的表
    private final List<String> TABLE_SETTING_FLYWAY = CollUtil.list(false, "act_ge_property", "act_id_property","hm_datasource", "hm_dictionary", "hm_datasource_env", "hm_folder", "hm_http_request_definition",
            "hm_http_service", "hm_menu", "hm_model_publish", "hm_published_entity_model", "hm_script", "hm_ureport_file", "hm_user_report", "http_service_setting", "hm_entity_model",
            "hm_model", "hm_page", "hm_perm", "hm_role", "hm_role_menu", "qrtz_blob_triggers", "qrtz_calendars", "qrtz_cron_triggers", "qrtz_job_details",
            "qrtz_simple_triggers", "qrtz_simprop_triggers", "qrtz_triggers");
    //只同步系统默认的数据  第一次打包时有效
    private final List<String> TABLE_DEFAULT_DATA_FLYWAY = CollUtil.list(false, "hm_org", "hm_user", "hm_user_group");
    //只同步表结构
    private final List<String> TABLE_STRUCTURE_FLYWAY = CollUtil.list(false, "act_ge_bytearray", "act_hi_actinst", "act_hi_attachment"

            , "act_id_bytearray", "act_id_group", "act_id_info", "act_id_membership",
            "act_id_priv", "act_id_priv_mapping", "act_id_token", "act_id_user", "act_procdef_info", "act_re_deployment", "act_re_model",
            "act_re_procdef", "flw_channel_definition", "flw_ev_databasechangelog", "flw_ev_databasechangeloglock", "flw_event_definition", "flw_event_deployment",
            "flw_event_resource", "act_evt_log");
    private Long sequence = 1L;
    private List<String> modelSqlList = null;

    /**
     * Creates a new instance of Analyser
     *
     * @throws java.lang.Exception
     */
    public ExportRunner(ConnectionDefinitionBean src, ConnectionDefinitionBean dst, Long sequence, List<String> modelSqlList)
            throws Exception {
        SRC = src;
        DST = dst;

        ignoreEmpty = false;
        //第几次打包
        this.sequence = sequence;
        this.modelSqlList = modelSqlList;

        CON_SRC = DBConnector.getConnection(SRC.getUrl(), SRC.getUser(), SRC.getPassword(), ignoreEmpty, SRC.getSchema());
        List<TableDefinition> v = orderedTableDefinitions(CON_SRC.getTableList());
        TABLES = v.toArray(new TableDefinition[v.size()]);

        SRC_TR = HelperManager.getTranslator(SRC.getUrl());
        DEST_TR = HelperManager.getTranslator(DST.getUrl());
    }

    private static boolean testRow(Virtual2DArray array, int row) {
        boolean rowOk = true;
        try {
            array.get(row, 0);
        } catch (Exception ex) {
            rowOk = false;
        }
        return rowOk;
    }

    public void outputScript(File file) throws Exception {
        if (file.exists()) {
            file.delete();
        }
        try (FileWriter writer = new FileWriter(file, true)) {
            for (int i = 0; i < TABLES.length; ++i) {
                //不在列表里的表  除去
                if (!TABLE_DEFINITION_FLYWAY.contains(TABLES[i].saneName)) {
                    continue;
                }
                List<ColumnDefinition> list = CON_SRC.getColumnList(TABLES[i]);
                //第一次打包 同步表结构
                if (sequence == 1) {
                    StringBuilder buff = new StringBuilder("CREATE TABLE ");
                    buff.append(DEST_TR.getCreateTablePrefix());
                    buff.append(TABLES[i].saneName);
                    buff.append(" ( ");
                    int j = 0;
                    for (ColumnDefinition def : list) {
                        if (j != 0) {
                            buff.append(", ");
                        }
                        buff.append(DEST_TR.outputName(def.name.saneName));
                        buff.append(" ");
                        buff.append(translate(def.sqlTypeName, def.sqlSize));
                        ++j;
                    }
                    buff.append("); ");

//                    writer.append(DEST_TR.getDropTable(TABLES[i].toString()));
//                    writer.append(";");
                    writer.append("\n");
                    writer.append(buff);
                    writer.append("\n");


                    addPrimaryKey(TABLES[i], writer);
                    addUniques(TABLES[i], writer);
                    addForeignKeys(TABLES[i], writer);
                }

                if (includeData) {
                    if (!TABLE_SETTING_FLYWAY.contains(TABLES[i].saneName) && !TABLE_DEFAULT_DATA_FLYWAY.contains(TABLES[i].saneName)) {
                        continue;
                    }
                    //只同步表结构的表 跳过
                    if(TABLE_STRUCTURE_FLYWAY.contains(TABLES[i].saneName)){
                        continue;
                    }
                    //只第一次同步数据
                    if (TABLE_DEFAULT_DATA_FLYWAY.contains(TABLES[i].saneName) && sequence > 1) {
                        continue;
                    }
                    Virtual2DArray fullTable = CON_SRC.getFullTable(TABLES[i]);
                    int rowCount = CON_SRC.getRowCount(TABLES[i]);
                    generateDataSql(fullTable, TABLES[i].saneName, list, rowCount, writer);
                }

            }

            for (String modelSql : modelSqlList) {
                writer.append(modelSql);
                writer.append("\n");
            }
        }
    }

    private void generateDataSql(Virtual2DArray rs, String table, List<ColumnDefinition> columns, int rowCount, FileWriter writer) throws Exception {
        if (rowCount == 0) {
            return;
        }
        boolean hasData = rs != null;
        if (hasData) {
            hasData = testRow(rs, 0);
        }
        if (!hasData) {
            return;
        }
        Helper tr = HelperManager.getTranslator(DST.getUrl());
        int columnCount = columns.size();
        //默认数据的表 只同步第一条
        if (TABLE_DEFAULT_DATA_FLYWAY.contains(table)) {
            rowCount = 1;
        }
        writer.append("delete from " + table + ";");
        writer.append("\n");
        for (int i = 0; i < rowCount; i++) {
            StringBuilder buff = new StringBuilder("INSERT INTO ");
            StringBuilder args = new StringBuilder();
            buff.append(table);
            buff.append(" ( ");

            for (int j = 0; j < columnCount; ++j) {
                if (j != 0) {
                    buff.append(", ");
                    args.append(", ");
                }
                buff.append(tr.outputName(columns.get(j).name.saneName));
                args.append(getArgStr(columns.get(j), rs.get(i, j)));

            }
            buff.append(" ) VALUES ( ");
            buff.append(args);
            buff.append(" );");
            writer.append(buff);
            writer.append("\n");
        }

        Optional<ColumnDefinition> keyDef = columns.stream().filter(e -> "serial".equals(e.sqlTypeName) || "bigserial".equals(e.sqlTypeName)).findFirst();
        if (!keyDef.isPresent()) {
            return;
        }
        fixSequences(CON_SRC, table, keyDef.get().sqlTypeName, keyDef.get().name.saneName, writer);
    }

    private String getArgStr(ColumnDefinition columnDefinition, Object data) {
        if (data == null) {
            return null;
        }
        System.out.println(columnDefinition.sqlTypeName + "  " + columnDefinition.name + "  " + data);
        String sqlType = columnDefinition.sqlTypeName;
        if ("int8".equals(sqlType) || "int4".equals(sqlType) || "serial".equals(sqlType) || "bigserial".equals(sqlType) || "numeric".equalsIgnoreCase(sqlType)) {
            return data.toString();
        }
        if ("varchar".equals(sqlType)) {
            return "'" + data.toString() + "'";
        }

        if ("timestamp".equals(sqlType)) {
            return "'" + data.toString() + "'";
        }
        if ("text".equals(sqlType)) {
            return "E'" + data.toString() + "'";
        }
        if ("bool".equals(sqlType)) {
            return data.toString();
        }
        if ("bytea".equals(sqlType)) {
            byte[] dataBytes = (byte[]) data;
            String hexStr = Hex.encodeHexString(dataBytes);
            return "decode('" + hexStr + "','hex')";
        }

        System.out.println("type:" + columnDefinition.sqlTypeName);
        return "";
    }

    public void fixSequences(DBConnection con, String table, String typeName, String column, FileWriter writer)
            throws Exception {
        if ("serial".equals(typeName) || "bigserial".equals(typeName)) {
            StringBuilder buff = new StringBuilder("SELECT MAX( ");
            buff.append(column);
            buff.append(" ) FROM ");
            buff.append(table);
            Object value = null;
            try {
                //				System.out.println( "C: " + buff );
                con.executeQuery("BEGIN;");
                Virtual2DArray rs = con.executeQuery(buff.toString());
                value = rs.getSingleValue();
                con.executeQuery("COMMIT;");
            } catch (SQLException ex) {
                if (ex.getMessage().indexOf("ultiple") == -1) {
                    throw ex;
                }
            }
            if (value != null) {
                buff = new StringBuilder("SELECT setval( '");
                buff.append(table);
                buff.append("_");
                buff.append(column);
                buff.append("_seq', ");
                buff.append(value);
                buff.append(" );");
                writer.append(buff);
                writer.append("\n");
            }
        }
    }

    private void addUniques(TableDefinition table, FileWriter writer) throws Exception {
        List<UniqueDefinition> uniqs = CON_SRC.getUniqueList(table);
        for (UniqueDefinition uniq : uniqs) {
            if (!"PRIMARY".equals(uniq.getOriginalName())) {
                StringBuilder buff = new StringBuilder("ALTER TABLE ");
                buff.append(table.saneName);
                buff.append(" ADD CONSTRAINT ");
                buff.append(uniq.getOutputName());
                buff.append(" UNIQUE ( ");
                buff.append(uniq.columns.get(0));

                for (int i = 1; i < uniq.columns.size(); ++i) {
                    buff.append(", ");
                    buff.append(uniq.columns.get(i));
                }
                buff.append(" );");

                writer.append(buff);
                writer.append("\n");

            }
        }
    }

    private List<TableDefinition> orderedTableDefinitions(List<TableDefinition> tables) throws Exception {
        List<TableDefinition> outPutList = new ArrayList<>();
        for (TableDefinition tableDefinition : tables) {
            reOrder(outPutList, tableDefinition);
        }
        return outPutList;
    }

    private void reOrder(List<TableDefinition> outPutList, TableDefinition tableDefinition) throws Exception {
        if (outPutList.contains(tableDefinition)) {
            return;
        }
        List<ForeignKeyDefinition> fks = CON_SRC.getForeignKeyList(tableDefinition);
        if (fks.isEmpty()) {
            outPutList.add(tableDefinition);
        } else {
            List<TableDefinition> tableDefinitions = fks.stream().flatMap(e -> e.columns.stream().map(l -> l.referencedTable)).distinct().collect(Collectors.toList());
            System.out.println("table " + tableDefinition.originalName + "has " + tableDefinitions.size() + " foreign key");

            for (TableDefinition definition : tableDefinitions) {
                //防止循环依赖 尤其是parent_id这种类型
                if (definition.equals(tableDefinition)) {
                    continue;
                }
                reOrder(outPutList, definition);
            }
            outPutList.add(tableDefinition);
        }
    }


    private void addForeignKeys(TableDefinition table, FileWriter writer) throws Exception {
        // FOREIGN KEYS
        List<ForeignKeyDefinition> imported = CON_SRC.getForeignKeyList(table);
        System.out.println("table: " + table.saneName + " has " + imported.size() + " possible fks");
        int i = 1;
        List<String> nameList = new ArrayList<>();

        for (ForeignKeyDefinition fk : imported) {
            if (ignoreEmpty && CON_SRC.getRowCount(fk.columns.get(0).referencedTable) == 0) {
                continue;
            }
            //去重
            if (nameList.contains(fk.name)) {
                continue;
            }
//			System.out.println( "FK: " + fk.name + " to " + fk.columns.get( 0 ).referencedTable );
            StringBuilder buff = new StringBuilder("ALTER TABLE ");
            buff.append(table.saneName);
            buff.append(" ADD CONSTRAINT ");
            buff.append(fk.getOutputName() + "_" + i);
            i++;
            buff.append(" FOREIGN KEY ( ");
            ColumnDefinition col0 = fk.columns.remove(0);
            buff.append(col0.name.saneName);
            for (ColumnDefinition col : fk.columns) {
                buff.append(", ");
                buff.append(col.name.saneName);
            }
            buff.append(" ) REFERENCES ");
            buff.append(col0.referencedTable.saneName);
            buff.append("( ");
            buff.append(col0.referencedColumn.name.saneName);
            for (ColumnDefinition col : fk.columns) {
                buff.append(", ");
                buff.append(col.referencedColumn.name.saneName);
            }
            buff.append(" );");
            writer.append(buff);
            writer.append("\n");
            nameList.add(fk.name);
        }
    }

    private void addPrimaryKey(TableDefinition table, FileWriter writer) throws Exception {
        // PRIMARY KEY
        PrimaryKeyDefinition key = CON_SRC.getPrimaryKey(table);
        if (!key.columns.isEmpty()) {
            StringBuilder buff = new StringBuilder("ALTER TABLE ");
            buff.append(table.saneName);
            buff.append(" ADD PRIMARY KEY ( ");
            buff.append(key.columns.remove(0).name.saneName);
            //		buff.append( " )" );
            for (ColumnDefinition col : key.columns) {
                buff.append(", ");
                buff.append(col.name.saneName);
            }
            buff.append(" );");
            writer.append(buff);
            writer.append("\n");
        }
    }

    private String translate(String type, Integer size) {
        return DEST_TR.outputType(SRC_TR.normalizedType(type), size);
    }
}

